﻿CREATE PROC [dbo].[sp_getDailySumData](@startDate datetime)
as 
begin 
	declare @endDate datetime 
	select @endDate = GETDATE()
  
	if exists(select * from tempdb.dbo.sysobjects where  name  like N'%#dateTable%' and type='U')
	drop table #dateTable 

	create table #dateTable ([date] varchar(10))        

	declare @dtTemp as datetime 
	select @dtTemp = @startDate   
	while (@dtTemp <= @endDate)
	begin 
	insert into #dateTable([date]) select Convert(varchar,@dtTemp,23)
	select @dtTemp =DATEADD(day,1, @dtTemp)
	end 
	
    --bind card 
    
    select t1.[date],t1.bindCnt,t2.apptCnt,(t2.apptMoney + t3.prescMoney - t3.yibaoMoney ) as daliyMoney,t5.ApptMoney + t6.prescMoney - t6.yibaoMoney  as sumMoney from 
    
    (select a.[date],coalesce(b.bindCnt,0) as bindCnt from #dateTable a left join 
	(select CONVERT(varchar,RecCreateDt,23) as [date] ,COUNT(*) as bindCnt 
	from HospCard where RecCreateDt between  @startDate and  @endDate 
	GROUP by CONVERT(varchar,RecCreateDt,23)) b 
	on a.[date] = b.[date]) t1 ,

	--appt order 
	(select a.[date],coalesce(b.apptCnt,0) as apptCnt,coalesce(b.apptMoney,0) as apptMoney from #dateTable a left join 
	(select Convert(varchar,FetchTime,23) [date],COUNT(*) as apptCnt,SUM(SumFee) as ApptMoney
	from ApptOrder where PayState =1  and  (OrderStatus = 21 or OrderStatus = 23)  and FetchTime between  @startDate and  @endDate 
	group by  Convert(varchar,FetchTime,23)) b 
	on a.[date] = b.[date]) t2,
	
	--bill order 
	(select a.[date],coalesce(b.prescMoney,0) as prescMoney,coalesce(b.yibaoMoney,0) as yibaoMoney from #dateTable a left join 
	(select Convert(varchar,FetchTime,23) [date] ,sum(PrescMoney) as  prescMoney , 
	SUM(case when YibaoMoney is null then 0 else YibaoMoney end ) as yibaoMoney
	from BillOrder where PayState = 1 and OrderStatus = 8  and FetchTime between  @startDate and  @endDate   
	group by Convert(varchar,FetchTime,23) ) b 
	on a.[date] = b.[date]) t3,

	----prepay order
	--(select a.[date],coalesce(b.PrepayMoney,0) as prepayMoney from #dateTable a left join 
	--(select Convert(varchar,fetchtime,23) [date] ,sum(PrepayMoney) as  PrepayMoney  
	--from InpatientPrepayOrder where PayState = 1 and OrderStatus = 8  and fetchtime between  @startDate and  @endDate   
	--group by Convert(varchar,fetchtime,23) ) b 
	--on a.[date] = b.[date]) t4,		
	
	--sum 						
	(	 	
		select c.[date],coalesce(d.ApptMoney,0) as ApptMoney from #dateTable c left join  
		(
			select a.[date] as [date],SUM(coalesce(sumFee,0)) as ApptMoney 
			from #dateTable a ,ApptOrder b 
			where b.paystate =1  and ( b.orderstatus = 21 or b.orderstatus = 23)  and b.FetchTime < DATEADD(day,1,a.[date])
			group by a.[date]
		)d on c.[date] = d.[date]
	) t5,
	
	(
		select c.[date],coalesce(d.prescMoney,0) as prescMoney,coalesce(d.yibaoMoney,0) as yibaoMoney from #dateTable c left join  
		(
			select a.[date],SUM(coalesce(b.prescMoney,0)) as prescMoney ,SUM(case when b.yibaojydjh is null then 0 else b.yibaoMoney end ) as yibaoMoney
			from #dateTable a ,BillOrder b 	
			where b.paystate =1  and  b.orderstatus = 8  and b.FetchTime < DATEADD(day,1,a.[date])
			group by a.[date]	
		) d on c.[date] = d.[date]
	) t6	
	
	--(
	--	select c.[date],coalesce(d.prepayMoney,0) as prepayMoney from #dateTable c left join  
	--	(
	--		select a.[date],SUM(coalesce(prepayMoney,0)) as prepayMoney 
	--		from #dateTable a ,InpatientPrepayOrder b 	
	--		where b.paystate =1  and  b.orderstatus = 8  and b.FetchTime < DATEADD(day,1,a.[date])
	--		group by a.[date]	
	--	)d	on c.[date] = d.[date]
	--)t7
		
	where t1.[date] = t2.[date] and t3.[date]= t2.[date] and t3.[date] = t5.[date] and t5.[date] = t6.[date] 
	
	order by 1

    --drop table 
	if exists(select * from tempdb..sysobjects where  name  like N'%#dateTable%' and type='U')
	drop table #dateTable 

end
